package com.webapp.dao;

import java.math.BigDecimal;
import java.util.Calendar;

import org.hibernate.SQLQuery;
import org.springframework.stereotype.Repository;

import com.webapp.domain.entity.IntegralChange;
@Repository("integralChangeDAO")
public class IntegralChangeDAO extends AbstractDAO<IntegralChange>{

	/**
	 * 根据咨询师与描述查询查询积分流水记录。
	 * @param counselorId
	 * @param describes
	 */
	public Object findDataByCounselorAndDescribes(long counselorId,String describes){
		String sql = "SELECT ic.* FROM INTEGRAL_CHANGE ic "+
		"LEFT JOIN INTEGRAL i ON ic.INTEGRAL_ID = i.id  "+
		"LEFT JOIN COUNSELOR c ON i.id = c.INTEGRAL_ID "+
		"WHERE ic.DESCRIBES = ? AND c.id = ? ";
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setParameter(0, describes);
		query.setParameter(1, counselorId);
		return query.uniqueResult();
	}
	
	/**
	 * 根据咨询师、描述、会话查询积分流水记录。  
	 * 如：判断该会话有没有上传深度咨询报告的积分添加
	 * @param counselorId
	 * @param describes
	 */
	public Object findDataByCounselorAndDescribes_session(long sessionId,long counselorId,String describes){
		String sql = "SELECT ic.* FROM INTEGRAL_CHANGE ic "+
				"LEFT JOIN INTEGRAL i ON ic.INTEGRAL_ID = i.id "+  
				"LEFT JOIN COUNSELOR c ON i.id = c.INTEGRAL_ID "+ 
				"LEFT JOIN SESSION s ON c.ID = s.COUNSELOR_ID "+
				"WHERE s.ID = ? AND ic.DESCRIBES = ? AND c.id = ?"; 
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setParameter(0, sessionId);
		query.setParameter(1, describes);
		query.setParameter(2, counselorId);
		return query.uniqueResult();
	}
	
	
	
	
	/**
	 * 根据用户与描述，查询查询积分流水记录。
	 * @param counselorId
	 * @param describes
	 */
	public Object findDataByUserAndDescribes(long userId,String describes){
		String sql = "SELECT ic.* FROM INTEGRAL_CHANGE ic "+
				"LEFT JOIN INTEGRAL i ON ic.INTEGRAL_ID = i.ID  "+
				"LEFT JOIN USER u ON i.ID = u.INTEGRAL_ID  "+
				"WHERE ic.DESCRIBES = ? AND u.ID = ? ";
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setParameter(0, describes);
		query.setParameter(1, userId);
		return query.uniqueResult();
	}
	
	/**
	 * 查询用户当天发xx类型添加的总积分
	 * @param userId 用户id
	 * @return 当天发起人工咨询添加的总积分
	 */
	public BigDecimal getSum_CHANGE_SCORE(long userId,String describes){
		Calendar now = Calendar.getInstance();
		String nowDay = now.get(Calendar.YEAR)+"-"+(now.get(Calendar.MONTH)+1)+"-"+now.get(Calendar.DAY_OF_MONTH);
		String sql = "SELECT SUM(ic.CHANGE_SCORE) FROM INTEGRAL_CHANGE ic "+
				"LEFT JOIN INTEGRAL i ON  ic.INTEGRAL_ID = i.ID "+
				"LEFT JOIN USER u ON i.ID = u.INTEGRAL_ID "+
				"WHERE ic.DESCRIBES = ? " +
				"AND ic.CREATE_DATE BETWEEN '"+nowDay+" 00:00:00' AND '"+nowDay+" 23:59:59' " +
				"AND u.ID = ?";
		SQLQuery query = getSession().createSQLQuery(sql);
		query.setParameter(0, describes);
		query.setParameter(1, userId);
		BigDecimal big = (BigDecimal) query.uniqueResult();
		big = big==null? new BigDecimal(0):big;
	    return big;
	}
}
